﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class OfficeTranshistoryDao
    {
        public bool Delete(SqlConnection conn, int transId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE @TransFlag\t\tint ");
            builder.Append("DECLARE @Quantity\t\tint ");
            builder.Append("DECLARE @EffectQuantity\tint ");
            builder.Append("SELECT ");
            builder.Append("\t@TransFlag = [TransFlag], ");
            builder.Append("\t@Quantity = [Quantity] ");
            builder.Append("FROM ");
            builder.Append("\t [dbo].[Office_Transhistory] ");
            builder.Append("WHERE ");
            builder.Append("\t[TransId] = @TransId ");
            builder.Append("IF @TransFlag = 0 ");
            builder.Append("\tSET @EffectQuantity = 0-@Quantity ");
            builder.Append("ELSE IF @TransFlag = 3 ");
            builder.Append("\tSET @EffectQuantity = 0-@Quantity ");
            builder.Append("ELSE ");
            builder.Append("\tSET @EffectQuantity = @Quantity ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Office_Product] ");
            builder.Append("SET ");
            builder.Append("\t[Stock] = [Stock] + @EffectQuantity ");
            builder.Append("WHERE ");
            builder.Append("\t[ProductId] IN( SELECT ProductId FROM [dbo].[Office_Transhistory] ");
            builder.Append("\t\t\tWHERE ");
            builder.Append("\t\t\t\t[TransId] = @TransId) ");
            builder.Append("DELETE FROM [dbo].[Office_Transhistory] ");
            builder.Append("WHERE ");
            builder.Append("\t[TransId] = @TransId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@TransId", SqlDbType.Int) };
            commandParameters[0].Value = transId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, OfficeTranshistoryData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE @EffectQuantity\tint ");
            builder.Append("IF @TransFlag = 0 ");
            builder.Append("\tSET @EffectQuantity = @Quantity ");
            builder.Append("ELSE IF @TransFlag = 3 ");
            builder.Append("\tSET @EffectQuantity = @Quantity ");
            builder.Append("ELSE ");
            builder.Append("\tSET @EffectQuantity = (0-@Quantity) ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Office_Product] ");
            builder.Append("SET ");
            builder.Append("\t[Stock] = [Stock] + @EffectQuantity ");
            builder.Append("WHERE ");
            builder.Append("\t[ProductId] = @ProductId ");
            builder.Append("INSERT INTO [dbo].[Office_Transhistory] ");
            builder.Append("( ");
            builder.Append("\t[ProductId], ");
            builder.Append("\t[Borrower], ");
            builder.Append("\t[TransFlag], ");
            builder.Append("\t[ProcessFlag], ");
            builder.Append("\t[Quantity], ");
            builder.Append("\t[Price], ");
            builder.Append("\t[Remark], ");
            builder.Append("\t[AddDate], ");
            builder.Append("\t[Operator] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@ProductId, ");
            builder.Append("\t@Borrower, ");
            builder.Append("\t@TransFlag, ");
            builder.Append("\t@ProcessFlag, ");
            builder.Append("\t@Quantity, ");
            builder.Append("\t@Price, ");
            builder.Append("\t@Remark, ");
            builder.Append("\t@AddDate, ");
            builder.Append("\t@Operator ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[9];
            commandParameters[0] = new SqlParameter("@ProductId", SqlDbType.Int);
            commandParameters[0].Value = data.ProductId;
            commandParameters[1] = new SqlParameter("@Borrower", SqlDbType.NVarChar, 20);
            if (data.BorrowerIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.Borrower;
            }
            commandParameters[2] = new SqlParameter("@TransFlag", SqlDbType.Int);
            commandParameters[2].Value = data.TransFlag;
            commandParameters[3] = new SqlParameter("@ProcessFlag", SqlDbType.Bit);
            commandParameters[3].Value = data.ProcessFlag;
            commandParameters[4] = new SqlParameter("@Quantity", SqlDbType.Int);
            commandParameters[4].Value = data.Quantity;
            commandParameters[5] = new SqlParameter("@Price", SqlDbType.Decimal);
            commandParameters[5].Precision = 10;
            commandParameters[5].Scale = 2;
            if (data.PriceIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.Price;
            }
            commandParameters[6] = new SqlParameter("@Remark", SqlDbType.NText);
            if (data.RemarkIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Remark;
            }
            commandParameters[7] = new SqlParameter("@AddDate", SqlDbType.DateTime);
            commandParameters[7].Value = data.AddDate;
            commandParameters[8] = new SqlParameter("@Operator", SqlDbType.NVarChar, 20);
            commandParameters[8].Value = data.Operator;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByTransId(SqlConnection conn, int transId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[Office_Transhistory].[TransId], ");
            builder.Append("\t[Office_Transhistory].[ProductId], ");
            builder.Append("\t[Office_Transhistory].[Borrower], ");
            builder.Append("\tdbo.GetUserName([Office_Transhistory].[Borrower]) AS BorrowerName, ");
            builder.Append("\t[Office_Transhistory].[TransFlag], ");
            builder.Append("\t[Office_Transhistory].[ProcessFlag], ");
            builder.Append("\t[Office_Transhistory].[Quantity], ");
            builder.Append("\t[Office_Transhistory].[Price], ");
            builder.Append("\t[Office_Transhistory].[Remark], ");
            builder.Append("\t[Office_Transhistory].[AddDate], ");
            builder.Append("\t[Office_Transhistory].[Operator], ");
            builder.Append("\tdbo.GetUserName([Office_Transhistory].[Operator]) AS OperatorName, ");
            builder.Append("\t [Office_Product].[Name] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Office_Transhistory], ");
            builder.Append("\t[dbo].[Office_Product] ");
            builder.Append("WHERE ");
            builder.Append("\t[Office_Transhistory].[TransId] = @TransId ");
            builder.Append("\tAND [Office_Transhistory].[ProductId] = [Office_Product].[ProductId] ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@TransId", SqlDbType.Int) };
            commandParameters[0].Value = transId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Office_Transhistory";
            return set.Tables[0];
        }

        public DataTable SelectPaged(SqlConnection conn, int pagesize, int pageindex, bool docount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([TransId]) FROM [dbo].[Office_Transhistory] ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [TransId] FROM [dbo].[Office_Transhistory] ORDER BY [TransId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Office_Transhistory].*,\t\t\t ");
            builder.Append("\t\t\tdbo.GetUserName([Office_Transhistory].[Borrower]) AS BorrowerName, ");
            builder.Append("\t\t\t[Office_Product].[Name], ");
            builder.Append("\t\t\t[Office_Product].[Stock], ");
            builder.Append("\t\t\t[Office_Product].[Unit], ");
            builder.Append("\t\t\t[Users].[UserName] AS OperatorName, ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Office_Transhistory], ");
            builder.Append("\t\t\t[dbo].[Office_Product], ");
            builder.Append("\t\t\t[dbo].[Users], ");
            builder.Append("\t\t\t@IndexTable t\t\t\t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Office_Transhistory].[ProductId] = [Office_Product].[ProductId] ");
            builder.Append("\t\t\tAND [Office_Transhistory].[Operator] = [Users].[UserId] ");
            builder.Append("\t\t\tAND [Office_Transhistory].[TransId] = t.PK\t\t\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[Office_Transhistory].[TransId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Office_Transhistory";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, OfficeTranshistoryData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE @OldTransFlag\tint ");
            builder.Append("DECLARE @OldQuantity\t\tint ");
            builder.Append("DECLARE @EffectQuantity\tint ");
            builder.Append("SELECT ");
            builder.Append("\t@OldTransFlag = [TransFlag], ");
            builder.Append("\t@OldQuantity = [Quantity] ");
            builder.Append("FROM ");
            builder.Append("\t [dbo].[Office_Transhistory] ");
            builder.Append("WHERE ");
            builder.Append("\t[TransId] = @TransId ");
            builder.Append("IF @OldTransFlag = 0 ");
            builder.Append("\tSET @EffectQuantity = 0-@OldQuantity ");
            builder.Append("ELSE IF @TransFlag = 3 ");
            builder.Append("\tSET @EffectQuantity = 0-@OldQuantity ");
            builder.Append("ELSE ");
            builder.Append("\tSET @EffectQuantity = @OldQuantity ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Office_Product] ");
            builder.Append("SET ");
            builder.Append("\t[Stock] = [Stock] + @EffectQuantity ");
            builder.Append("WHERE ");
            builder.Append("\t[ProductId] IN( SELECT ProductId FROM [dbo].[Office_Transhistory] ");
            builder.Append("\t\t\tWHERE ");
            builder.Append("\t\t\t\t[TransId] = @TransId) ");
            builder.Append("IF @TransFlag = 0 ");
            builder.Append("\tSET @EffectQuantity = @Quantity ");
            builder.Append("ELSE IF @TransFlag = 3 ");
            builder.Append("\tSET @EffectQuantity = @Quantity ");
            builder.Append("ELSE ");
            builder.Append("\tSET @EffectQuantity = (0-@Quantity) ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Office_Product] ");
            builder.Append("SET ");
            builder.Append("\t[Stock] = [Stock] + @EffectQuantity ");
            builder.Append("WHERE ");
            builder.Append("\t[ProductId] = @ProductId ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Office_Transhistory] ");
            builder.Append("SET ");
            builder.Append("\t[ProductId] = @ProductId, ");
            builder.Append("\t[Borrower] = @Borrower, ");
            builder.Append("\t[TransFlag] = @TransFlag, ");
            builder.Append("\t[ProcessFlag] = @ProcessFlag, ");
            builder.Append("\t[Quantity] = @Quantity, ");
            builder.Append("\t[Price] = @Price, ");
            builder.Append("\t[Remark] = @Remark, ");
            builder.Append("\t[AddDate] = @AddDate, ");
            builder.Append("\t[Operator] = @Operator ");
            builder.Append("WHERE ");
            builder.Append("\t[TransId] = @TransId ");
            SqlParameter[] commandParameters = new SqlParameter[10];
            commandParameters[0] = new SqlParameter("@TransId", SqlDbType.Int);
            commandParameters[0].Value = data.TransId;
            commandParameters[1] = new SqlParameter("@ProductId", SqlDbType.Int);
            commandParameters[1].Value = data.ProductId;
            commandParameters[2] = new SqlParameter("@Borrower", SqlDbType.NVarChar, 20);
            if (data.BorrowerIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.Borrower;
            }
            commandParameters[3] = new SqlParameter("@TransFlag", SqlDbType.Int);
            commandParameters[3].Value = data.TransFlag;
            commandParameters[4] = new SqlParameter("@ProcessFlag", SqlDbType.Bit);
            commandParameters[4].Value = data.ProcessFlag;
            commandParameters[5] = new SqlParameter("@Quantity", SqlDbType.Int);
            commandParameters[5].Value = data.Quantity;
            commandParameters[6] = new SqlParameter("@Price", SqlDbType.Decimal);
            commandParameters[6].Precision = 10;
            commandParameters[6].Scale = 2;
            if (data.PriceIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Price;
            }
            commandParameters[7] = new SqlParameter("@Remark", SqlDbType.NText);
            if (data.RemarkIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Remark;
            }
            commandParameters[8] = new SqlParameter("@AddDate", SqlDbType.DateTime);
            commandParameters[8].Value = data.AddDate;
            commandParameters[9] = new SqlParameter("@Operator", SqlDbType.NVarChar, 20);
            commandParameters[9].Value = data.Operator;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

